﻿Public Class TapHoaDAO
    Public Shared Function ThongTinTatCaSanPham() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HangHoa"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayThongTinMotSanPham(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HangHoa HH,DonViTinh DVT,NhaCungCap NCC,XuatXu XX,LoaiHang LH ,ViTri VT where HH.hh_TenHang ='" + _th.m_strTenHangHoa + "' and HH.hh_DonVi = DVT.dvt_ID and HH.hh_NhaCungCap = NCC.ncc_ID and HH.hh_XuatXu = XX.xx_ID and HH.hh_LoaiHang = LH.lh_ID and HH.hh_ViTri = VT.vt_ID"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayThongTinMotSanPhamTheoID(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HangHoa where hh_ID =" & _th.m_intIDSanPham
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayThongTinMotSanPhamTheoTen(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HangHoa where hh_TenHang = '" + _th.m_strTenHangHoa + "'"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayTenViTri(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from ViTri where vt_ID = " & _th.m_intIDViTri
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayIDHoaDon(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "insert into HoaDon(hd_TongTien,hd_Ngay,hd_NhanVien) values ('" & _th.m_intTongTien & " ','" & _th.m_dtNgayGio & "','" & _th.m_intIDNhanVien & "')"
        Dim strtest As String = "SELECT MAX(hd_ID) as ID from HoaDon"
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
        dt = dp.ExecuteQuery(strtest)
        Return dt
    End Function

    Public Shared Function LayIDNhapHang(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "insert into NhapHang(nh_NgayNhap,nh_NguoiNhap,nh_TongSoSanPham,nh_TongTien) values ('" & _th.m_dtNgayGio & " ','" & _th.m_intIDNhanVien & "','" & _th.m_intSoLuong & "','" & _th.m_intTongTien & "')"
        Dim strtest As String = "SELECT MAX(nh_ID) as ID from NhapHang"
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
        dt = dp.ExecuteQuery(strtest)
        Return dt
    End Function

    Public Shared Function LayThongTinSanPhamDaMua(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from ChiTietDonHang where ctdh_HoaDon = " & _th.m_intMaHoaDon
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Sub ThemChiTietDonHang(ByVal _th As TapHoa)
        Dim strSQL As String = "insert into ChiTietDonHang(ctdh_HoaDon,ctdh_TenHang,ctdh_SoLuong,ctdh_Gia) values ('" & _th.m_intMaHoaDon & " ','" & _th.m_intIDSanPham & "','" & _th.m_intSoLuong & "','" & _th.m_intTongGia & "')"
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
    End Sub

    Public Shared Function DangNhap(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from TaiKhoan where tk_TenDangNhap = '" + _th.m_strTenDangNhap + "' and tk_MatKhau = '" + _th.m_strMatKhau + "'"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayThongTinTaiKhoanID(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from TaiKhoan where tk_ID =" & _th.m_intIDTaiKhoan
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function TimKiemSanPhamTheoTen(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HangHoa where hh_TenHang LIKE '%" + _th.m_strTenHangHoa + "%'"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function TimKiemSanPhamTheoTieuChi(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HangHoa where hh_Gia " + _th.m_strSoSanhGia + "" & _th.m_intGia & "and hh_SoLuong" + _th.m_strSoSanhSoLuong + "" & _th.m_intSoLuong & "and hh_LoaiHang = " & _th.m_intIDLoaiHang & "and hh_NhaCungCap = " & _th.m_intIDNhaCungCap & "and hh_XuatXu = " & _th.m_intIDXuatXu

        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function TatCaLoaiHang() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from LoaiHang"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Sub TruSoLuongHangHoa(ByVal _th As TapHoa)
        Dim strSQL As String = "update HangHoa set hh_SoLuong = hh_SoLuong - '" & _th.m_intSoLuong & "' where hh_TenHang = '" + _th.m_strTenHangHoa + "' "
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
    End Sub

    Public Shared Function TatCaNhaCungCap() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from NhaCungCap"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function TatCaXuatXu() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from XuatXu"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function TatCaViTri() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from ViTri"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function TatCaDonVi() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from DonViTinh"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function TimHangHoaTheoTen(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HangHoa where hh_TenHang = '" + _th.m_strTenHangHoa + "'"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function CapNhatSoLuongSanPham(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "update HangHoa set hh_SoLuong = hh_SoLuong + '" & _th.m_intSoLuong & "' where hh_TenHang = '" + _th.m_strTenHangHoa + "' "
        Dim strtest As String = "SELECT hh_ID as ID from HangHoa where hh_TenHang = '" + _th.m_strTenHangHoa + "' "
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
        dt = dp.ExecuteQuery(strtest)
        Return dt
    End Function

    Public Shared Function ThemHangHoa(ByVal _th As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "insert into HangHoa(hh_TenHang,hh_Gia,hh_DonVi,hh_SoLuong,hh_NhaCungCap,hh_XuatXu,hh_LoaiHang,hh_ViTri,hh_HinhAnh) values ('" & _th.m_strTenHangHoa & " ','" & _th.m_intGia & "','" & _th.m_intIDDonVi & "','" & _th.m_intSoLuong & "','" & _th.m_intIDNhaCungCap & "','" & _th.m_intIDXuatXu & "','" & _th.m_intIDLoaiHang & "','" & _th.m_intIDViTri & "','" & _th.m_strHinhAnh & "')"
        Dim strtest As String = "SELECT MAX(hh_ID) as ID from HangHoa"
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
        dt = dp.ExecuteQuery(strtest)
        Return dt
    End Function

    Public Shared Sub ChiTietNhapHang(ByVal _th As TapHoa)
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "insert into ChiTietNhapHang(ctnh_DonNhapHang,ctnh_SanPham) values ('" & _th.m_intIDDonNhapHang & "','" & _th.m_intIDSanPham & "')"
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
    End Sub
	
    Public Shared Function LayHangHoafrmThemKho() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select HH.hh_ID,HH.hh_TenHang, HH.hh_Gia,HH.hh_DonVi,HH.hh_NhaCungCap,HH.hh_XuatXu,HH.hh_LoaiHang,HH.hh_ViTri, DVT.dvt_Loai, HH.hh_SoLuong, NCC.ncc_Ten, NCC.ncc_DiaChi, NCC.ncc_SoDienThoai, XX.xx_NoiXuatXu, LH.lh_Loai, VT.vt_NoiVT, HH.hh_HinhAnh from HangHoa HH, LoaiHang LH, NhaCungCap NCC, XuatXu XX, ViTri VT, DonViTinh DVT where HH.hh_DonVi = DVT.dvt_ID and HH.hh_NhaCungCap = NCC.ncc_ID and HH.hh_XuatXu = XX.xx_ID and HH.hh_LoaiHang = LH.lh_ID and HH.hh_ViTri = VT.vt_ID"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayTatCaViTri() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from ViTri"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayTatCaDonVi() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from DonViTinh"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayTatCaLoaiHang() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from LoaiHang"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayTatCaNhaCungCap() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from NhaCungCap"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Function LayTatCaXuatXu() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from XuatXu"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Public Shared Sub ThemHangHoaVaoKho(ByVal _th As TapHoa)
        Dim strSQL As String = "insert into HangHoa(hh_TenHang,hh_Gia,hh_DonVi,hh_SoLuong,hh_NhaCungCap,hh_XuatXu,hh_LoaiHang,hh_ViTri,hh_HinhAnh) values ('" & _th.m_strTenHangHoa & " ','" & _th.m_intGia & "','" & _th.m_intIDDonVi & "','" & _th.m_intSoLuong & "', '" & _th.m_intIDNhaCungCap & "', '" & _th.m_intIDXuatXu & "', '" & _th.m_intIDLoaiHang & "', '" & _th.m_intIDViTri & "', '" & _th.m_strHinhAnh & "')"
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
    End Sub

    Public Shared Sub CapNhatHangHoa(ByVal _th As TapHoa)
        Dim strSQL As String = "update HangHoa set hh_TenHang = '" & _th.m_strTenHangHoa & "', hh_Gia = '" & _th.m_intGia & "',hh_DonVi = '" & _th.m_intIDDonVi & "',hh_SoLuong = '" & _th.m_intSoLuong & "', hh_NhaCungCap = '" & _th.m_intIDNhaCungCap & "', hh_XuatXu = '" & _th.m_intIDXuatXu & "', hh_LoaiHang = '" & _th.m_intIDLoaiHang & "', hh_ViTri = '" & _th.m_intIDViTri & "', hh_HinhAnh = '" & _th.m_strHinhAnh & "' where  hh_ID = " & _th.m_intIDHangHoa
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
    End Sub

    Public Shared Sub XoaHangHoa(ByVal _hanghoa As TapHoa)
        Dim strSQL As String = "delete from HangHoa where  hh_ID = " & _hanghoa.m_intIDHangHoa
        Dim dp As DataProvider = New DataProvider()
        dp.ExecuteNonQuery(strSQL)
    End Sub

    Public Shared Function ThongKeSoLuong() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HoaDon HD, ChiTietDonHang CTDH where HD.hd_ID = CTDH.ctdh_HoaDon"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Shared Function LayDonViKho(ByVal DonVi As Integer) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from DonViTinh where dvt_ID = " & DonVi
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Shared Function LayNhaCungCapKho(NhaCungCap As Integer) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from NhaCungCap where ncc_ID = " & NhaCungCap
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Shared Function LayXuatXuKho(XuatXu As Integer) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from XuatXu where xx_ID = " & XuatXu
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Shared Function LayLoaiHangKho(LoaiHang As Integer) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from LoaiHang where lh_ID = " & LoaiHang
        Dim dp As DataProvider = New DataProvider
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Shared Function LayViTriKho(ViTri As Integer) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from ViTri where vt_ID = " & ViTri
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Shared Function LayTenHangHoa() As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select * from HangHoa"
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

    Shared Function LocTheoTenHangHoa(TapHoa As TapHoa) As DataTable
        Dim dt As DataTable = Nothing
        Dim strSQL As String = "select HH.hh_ID,HH.hh_TenHang, HH.hh_Gia,HH.hh_DonVi,HH.hh_NhaCungCap,HH.hh_XuatXu,HH.hh_LoaiHang,HH.hh_ViTri, DVT.dvt_Loai, HH.hh_SoLuong, NCC.ncc_Ten, NCC.ncc_DiaChi, NCC.ncc_SoDienThoai, XX.xx_NoiXuatXu, LH.lh_Loai, VT.vt_NoiVT, HH.hh_HinhAnh from HangHoa HH, LoaiHang LH, NhaCungCap NCC, XuatXu XX, ViTri VT, DonViTinh DVT where HH.hh_DonVi = DVT.dvt_ID and HH.hh_NhaCungCap = NCC.ncc_ID and HH.hh_XuatXu = XX.xx_ID and HH.hh_LoaiHang = LH.lh_ID and HH.hh_ViTri = VT.vt_ID and HH.hh_ID = " & TapHoa.m_intIDHangHoa
        Dim dp As DataProvider = New DataProvider()
        dt = dp.ExecuteQuery(strSQL)
        Return dt
    End Function

End Class
